{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "39d15ab7-1b81-4646-8760-3b11ea1c18bc",
   "metadata": {},
   "source": [
    "### SQL and Data Manipulation:\n",
    "\n",
    "1. What is a primary key and why is it important in database design?\n",
    "2. Explain the differences between a join, union, and subquery in SQL.\n",
    "3. How would you write a SQL query to find the second highest salary in a table?\n",
    "4. What is the purpose of indexing in a database? How does it improve query performance?\n",
    "5. Describe the difference between a LEFT JOIN and an INNER JOIN in SQL.\n",
    "6. How do you handle duplicate records in a database table?\n",
    "7. Explain the use of GROUP BY and HAVING clauses in SQL.\n",
    "8. What is a self-join in SQL and when would you use it?\n",
    "9. How would you calculate the moving average of a time series dataset in SQL?\n",
    "10. Describe the purpose of the CASE statement in SQL and provide an example."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f90a930-043f-484f-9e36-ae9af1b47871",
   "metadata": {},
   "source": [
    "### 1. What is a primary key and why is it important in database design?\n",
    "\n",
    "**Answer:**\n",
    "- **Primary Key:** A primary key is a unique identifier for a record in a database table. It ensures that each record is distinctly identifiable.\n",
    "  \n",
    "- **Importance:** \n",
    "  - Uniqueness: Guarantees uniqueness for each record.\n",
    "  - Referential Integrity: Enables relationships between tables.\n",
    "  - Indexing: Automatically creates an index, enhancing retrieval speed.\n",
    "\n",
    "---\n",
    "\n",
    "### 2. Explain the differences between a join, union, and subquery in SQL.\n",
    "\n",
    "**Answer:**\n",
    "- **Join:** Combines rows from two or more tables based on a related column.\n",
    "- **Union:** Combines the result sets of two or more SELECT statements into a single result set.\n",
    "- **Subquery:** A query nested within another query, used to retrieve data for comparison or inclusion in the main query.\n",
    "\n",
    "---\n",
    "\n",
    "### 3. How would you write a SQL query to find the second highest salary in a table?\n",
    "\n",
    "**Answer:**\n",
    "```sql\n",
    "SELECT MAX(salary) AS SecondHighestSalary\n",
    "FROM employees\n",
    "WHERE salary < (SELECT MAX(salary) FROM employees);\n",
    "```\n",
    "\n",
    "This query uses a subquery to find the maximum salary and then selects the maximum salary lower than that, effectively giving the second-highest salary.\n",
    "\n",
    "---\n",
    "\n",
    "### 4. What is the purpose of indexing in a database? How does it improve query performance?\n",
    "\n",
    "**Answer:**\n",
    "- **Purpose:** Indexing accelerates data retrieval operations on a database table.\n",
    "  \n",
    "- **Improves Query Performance:**\n",
    "  - Faster Data Retrieval: Reduces the number of rows scanned to find matching records.\n",
    "  - Speeds Up Search Operations: Facilitates quick lookup of specific values.\n",
    "  - Enhances Join Operations: Optimizes join conditions, especially in large tables.\n",
    "\n",
    "---\n",
    "\n",
    "### 5. Describe the difference between a LEFT JOIN and an INNER JOIN in SQL.\n",
    "\n",
    "**Answer:**\n",
    "- **INNER JOIN:** Retrieves rows from both tables only if there is a match based on the join condition. Non-matching rows are excluded.\n",
    "  \n",
    "- **LEFT JOIN (or LEFT OUTER JOIN):** Retrieves all rows from the left table and matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.\n",
    "\n",
    "---\n",
    "\n",
    "### 6. How do you handle duplicate records in a database table?\n",
    "\n",
    "**Answer:**\n",
    "- **Prevention:** Use primary keys to ensure uniqueness.\n",
    "- **Detection:** Run queries to identify duplicates using GROUP BY and HAVING clauses.\n",
    "- **Removal:** Delete duplicates based on certain criteria, keeping one record.\n",
    "\n",
    "---\n",
    "\n",
    "### 7. Explain the use of GROUP BY and HAVING clauses in SQL.\n",
    "\n",
    "**Answer:**\n",
    "- **GROUP BY:** Groups rows that have the same values in specified columns into summary rows.\n",
    "  \n",
    "- **HAVING:** Filters the results of a GROUP BY clause based on a specified condition.\n",
    "\n",
    "These clauses are often used together to perform aggregate functions on grouped data.\n",
    "\n",
    "---\n",
    "\n",
    "### 8. What is a self-join in SQL and when would you use it?\n",
    "\n",
    "**Answer:**\n",
    "- **Self-Join:** A self-join is a regular join, but the table is joined with itself.\n",
    "  \n",
    "- **Use Cases:** \n",
    "  - Hierarchical Relationships: When a table has hierarchical data, such as an organizational chart.\n",
    "  - Comparing Rows: When you need to compare rows within the same table.\n",
    "\n",
    "---\n",
    "\n",
    "### 9. How would you calculate the moving average of a time series dataset in SQL?\n",
    "\n",
    "**Answer:**\n",
    "```sql\n",
    "SELECT \n",
    "  date,\n",
    "  value,\n",
    "  AVG(value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average\n",
    "FROM time_series_data;\n",
    "```\n",
    "\n",
    "This query uses the window function `AVG` to calculate the moving average over a specified window of rows in the ordered dataset.\n",
    "\n",
    "---\n",
    "\n",
    "### 10. Describe the purpose of the CASE statement in SQL and provide an example.\n",
    "\n",
    "**Answer:**\n",
    "- **Purpose:** The CASE statement performs conditional logic in SQL queries.\n",
    "  \n",
    "- **Example:**\n",
    "```sql\n",
    "SELECT \n",
    "  employee_id,\n",
    "  salary,\n",
    "  CASE \n",
    "    WHEN salary > 50000 THEN 'High'\n",
    "    WHEN salary > 30000 THEN 'Moderate'\n",
    "    ELSE 'Low'\n",
    "  END AS salary_category\n",
    "FROM employees;\n",
    "```\n",
    "This example categorizes employees based on their salary into 'High', 'Moderate', or 'Low' using the CASE statement.\n",
    "\n",
    "These detailed answers provide comprehensive explanations for each question. If you have further questions or specific points you'd like to discuss, feel free to let me know!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
